/**
 * 
 */
package com.gfwy.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.gfwy.dao.INewsDao;
import com.gfwy.vo.News;

/**
 * @author Administrator
 *
 */
public class NewsDaoImpl implements INewsDao {
	private Connection conn = null;
	private PreparedStatement pstmt = null;
	private ResultSet rs = null;
	public NewsDaoImpl(Connection conn) {
		this.conn = conn;
	}
	/* (non-Javadoc)
	 * @see com.hospital.dao.INewsDao#doCreate(com.hospital.vo.News)
	 */
	@Override
	public boolean doCreate(News news) throws Exception {
		boolean flag = false;
		try {
			String sql = "INSERT INTO news(title,editor,pubdate,content,source,type,active,picnews,imgdir) VALUES (?,?,?,?,?,?,?,?,?)";
			this.pstmt = this.conn.prepareStatement(sql);
			this.pstmt.setString(1, news.getTitle());
			this.pstmt.setString(2, news.getEditor());
			
			this.pstmt.setTimestamp(3, new java.sql.Timestamp(news.getPubdate().getTime()));
			this.pstmt.setString(4, news.getContent());
			this.pstmt.setString(5, news.getSource());
			this.pstmt.setString(6, news.getType());
			this.pstmt.setBoolean(7, news.isActive());
			this.pstmt.setBoolean(8, news.isPicnews());
			this.pstmt.setString(9, news.getImgdir());
			if(this.pstmt.executeUpdate() > 0){
				flag = true;
			}
			
		} catch (Exception e) {
			throw e;
		} finally {
			if(this.pstmt != null) {
				try {
					this.pstmt.close();
				} catch (Exception e) {
					throw e;
				}
			}
			if(this.conn != null) {
				try {
					this.conn.close();
				} catch (Exception e) {
					throw e;
				}
			}
		}
		return flag;
	}
	@Override
	public List<News> findAll(String keyWord,int startIndex, int count, boolean picnews) throws Exception {
		List<News> list = null;
		try {
			if(picnews == false) {
				String sql = "SELECT * FROM news WHERE type <> ? AND type <> ? AND picnews=? AND (content LIKE ? OR title LIKE ?) ORDER BY pubdate DESC LIMIT ?,?";
				this.pstmt = this.conn.prepareStatement(sql);
				this.pstmt.setString(1, "zg");
				this.pstmt.setString(2, "sfsh");
				this.pstmt.setBoolean(3, false);
				this.pstmt.setString(4, "%" + keyWord + "%");
				this.pstmt.setString(5, "%" + keyWord + "%");
				this.pstmt.setInt(6, startIndex);
				this.pstmt.setInt(7, count);
			} else {
				String sql = "SELECT * FROM news WHERE type <> ? AND type <> ? AND  (content LIKE ? OR title LIKE ?) ORDER BY pubdate DESC LIMIT ?,?";
				this.pstmt = this.conn.prepareStatement(sql);
				this.pstmt.setString(1, "zg");
				this.pstmt.setString(2, "sfsh");
				this.pstmt.setString(3, "%" + keyWord + "%");
				this.pstmt.setString(4, "%" + keyWord + "%");
				this.pstmt.setInt(5, startIndex);
				this.pstmt.setInt(6, count);
			}
			
			this.rs = this.pstmt.executeQuery();
			list = new ArrayList<News>();
			while(rs.next()) {
				News news = new News();
				news.setTitle(rs.getString("title"));
				news.setNid(rs.getInt("nid"));
				news.setEditor(rs.getString("editor"));
				news.setPubdate(rs.getDate("pubdate"));
				news.setContent(rs.getString("content"));
				news.setType(rs.getString("type"));
				news.setSource(rs.getString("source"));
				news.setImgdir(rs.getString("imgdir"));
				list.add(news);
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if(this.rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
					throw e;
				}
			}
			if(this.pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e) {
					throw e;
				}
			}
			if(this.conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					throw e;
				}
			}
		}
		return list;
	}
	@Override
	public boolean doUpdate(News news) throws Exception {
		boolean flag = false;
		try {
			String sql = "UPDATE news SET title=?,editor=?,content=?,source=?,imgdir=?,picnews=? WHERE nid=?";
			this.pstmt = this.conn.prepareStatement(sql);
			this.pstmt.setString(1, news.getTitle());
			this.pstmt.setString(2, news.getEditor());
			this.pstmt.setString(3, news.getContent());
			this.pstmt.setString(4, news.getSource());
			this.pstmt.setString(5, news.getImgdir());
			this.pstmt.setBoolean(6, news.isPicnews());
			this.pstmt.setInt(7, news.getNid());
			
			if(this.pstmt.executeUpdate() > 0) {
				flag = true;
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if(this.pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e) {
					throw e;
				}
			}
			if(this.conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					throw e;
				}
			}
		}
		return flag;
	}
	@Override
	public int count(String type) throws Exception {
		int count = 0;
		try {
			String sql = "SELECT COUNT(*) AS count FROM news WHERE type=?";
			this.pstmt = this.conn.prepareStatement(sql);
			this.pstmt.setString(1, type);
			this.rs = this.pstmt.executeQuery();
			if(this.rs.next()) {
				count = this.rs.getInt("count");
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if(this.rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
					throw e;
				}
			}
			if(this.pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e) {
					throw e;
				}
			}
			if(this.conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					throw e;
				}
			}
		}
		return count;
	}
	@Override
	public News findById(int nid) throws Exception {
		News news = null;
		try {
			String sql = "SELECT * FROM news WHERE nid=?";
			this.pstmt = this.conn.prepareStatement(sql);
			this.pstmt.setInt(1, nid);
			this.rs = this.pstmt.executeQuery();
			
			if(this.rs.next()) {
				news = new News();
				news.setNid(this.rs.getInt("nid"));
				news.setEditor(this.rs.getString("editor"));
				
				news.setContent(this.rs.getString("content"));
				news.setPubdate(this.rs.getDate("pubdate"));
				news.setTitle(this.rs.getString("title"));
				news.setSource(this.rs.getString("source"));
				news.setType(this.rs.getString("type"));
			}
			
		} catch (Exception e) {
			throw e;
		} finally {
			if(this.rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
					throw e;
				}
			}
			if(this.pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e) {
					throw e;
				}
			}
			if(this.conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					throw e;
				}
			}
		}
		return news;
	}
	@Override
	public boolean doDel(int nid) throws Exception {
		boolean flag = false;
		try {
			String sql = "DELETE FROM news WHERE nid=?";
			this.pstmt = this.conn.prepareStatement(sql);
			this.pstmt.setInt(1, nid);
			if(this.pstmt.executeUpdate() > 0){
				flag = true;
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if(this.pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e) {
					throw e;
				}
			}
			if(this.conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					throw e;
				}
			}
		}
		return flag;
	}
	@Override
	public boolean doUpdateT(News news) throws Exception {
		boolean flag = false;
		try {
			String sql = "UPDATE news SET content=? WHERE type=?";
			this.pstmt = this.conn.prepareStatement(sql);
			
			this.pstmt.setString(1, news.getContent());
			this.pstmt.setString(2, "zg");
			if(this.pstmt.executeUpdate() > 0) {
				flag = true;
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if(this.pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e) {
					throw e;
				}
			}
			if(this.conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					throw e;
				}
			}
		}
		return flag;
	}
	@Override
	public List<News> findReferences(int nid, int size, String type) throws Exception {
		List<News> list = null;
		try {
			if("sfsh".equals(type)) {
				String sql = "SELECT * FROM news WHERE type=? AND nid <> ? ORDER BY pubdate DESC LIMIT 0, ?";
				this.pstmt = this.conn.prepareStatement(sql);
				this.pstmt.setString(1, type);
				this.pstmt.setInt(2, nid);
				this.pstmt.setInt(3, size);
			} else {
				String sql = "SELECT * FROM news WHERE type<>? AND type<>? AND nid <> ? ORDER BY pubdate DESC LIMIT 0, ?";
				this.pstmt = this.conn.prepareStatement(sql);
				this.pstmt.setString(1, "zg");
				this.pstmt.setString(2, "sfsh");
				this.pstmt.setInt(3, nid);
				this.pstmt.setInt(4, size);
			}
			
			this.rs = this.pstmt.executeQuery();
			list = new ArrayList<News>();
			while(rs.next()) {
				News news = new News();
				news.setTitle(rs.getString("title"));
				news.setNid(rs.getInt("nid"));
				news.setEditor(rs.getString("editor"));
				news.setPubdate(rs.getDate("pubdate"));
				news.setType(rs.getString("type"));
				news.setContent(rs.getString("content"));
				news.setSource(rs.getString("source"));
				list.add(news);
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if(this.rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
					throw e;
				}
			}
			if(this.pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e) {
					throw e;
				}
			}
		}
		return list;
	}
	@Override
	public List<News> findByType(String keyWord,int startIndex, int count, String type) throws Exception {
		List<News> list = null;
		try {
			
			String sql = "SELECT * FROM news WHERE type=? AND (content LIKE ? OR title LIKE ?) ORDER BY pubdate DESC LIMIT ?,?";
			this.pstmt = this.conn.prepareStatement(sql);
			this.pstmt.setString(1, type);
			this.pstmt.setString(2, "%" + keyWord + "%");
			this.pstmt.setString(3, "%" + keyWord + "%");
			this.pstmt.setInt(4, startIndex);
			this.pstmt.setInt(5, count);
			
			
			this.rs = this.pstmt.executeQuery();
			list = new ArrayList<News>();
			while(rs.next()) {
				News news = new News();
				news.setTitle(rs.getString("title"));
				news.setNid(rs.getInt("nid"));
				news.setEditor(rs.getString("editor"));
				news.setPubdate(rs.getDate("pubdate"));
				news.setContent(rs.getString("content"));
				news.setType(rs.getString("type"));
				news.setSource(rs.getString("source"));
				news.setImgdir(rs.getString("imgdir"));
				list.add(news);
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if(this.rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
					throw e;
				}
			}
			if(this.pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e) {
					throw e;
				}
			}
			if(this.conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					throw e;
				}
			}
		}
		return list;
	}
	@Override
	public int count(String type, String keyWord) throws Exception {
		int count = 0;
		try {
			String sql = "SELECT COUNT(*) AS count FROM news WHERE type=? AND (title LIKE ? OR content LIKE ?)";
			this.pstmt = this.conn.prepareStatement(sql);
			System.out.println(type);
			this.pstmt.setString(1, type);
			this.pstmt.setString(2, "%" + keyWord +"%");
			this.pstmt.setString(3, "%" + keyWord +"%");
			this.rs = this.pstmt.executeQuery();
			if(this.rs.next()) {
				count = this.rs.getInt("count");
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if(this.rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
					throw e;
				}
			}
			if(this.pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e) {
					throw e;
				}
			}
			if(this.conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					throw e;
				}
			}
		}
		return count;
	}

}
